﻿using System;
using System.Data;
using System.Collections.Generic;
using TCKQKL.DAL.IDAL;
using TCKQKL.Service.DataContract;
using System.Data.SqlClient;
using TCKQKL.DAL.Config;

namespace TCKQKL.DAL.SQLserver
{
    public class OrdersOnline : IOrdersOnline
    {
        private SqlConnection _internalConnection;
        private SqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        private const string GET_ORDER_DETAIL = "SELECT * FROM V_ORDERPLACED WHERE (1=1) ";

        #region Parameters
        private const string PARM_ORDERID = "@ORDERID";
        private const string PARM_ACCOUNTNO = "@ACCOUNTNO";
        private const string PARM_ORDERSIDE = "@ORDERSIDE";
        private const string PARM_SYMBOL = "@SYMBOL";
        private const string PARM_ORDERSPEC = "@ORDERSPEC";
        private const string PARM_ORDERCLASS = "@ORDERCLASS";
        private const string PARM_ORDERMODE = "@ORDERMODE";
        private const string PARM_OPENDATE = "@OPENDATE";
        private const string PARM_EXPIREDDATE = "@EXPIREDDATE";
        private const string PARM_ORDERNO = "@ORDERNO";
        private const string PARM_TIMESTAMP = "@TIMESTAMP";
        private const string PARM_PRICE = "@PRICE";
        private const string PARM_QUANTITY = "@QUANTITY";
        private const string PARM_ORDERTYPE = "@ORDERTYPE";
        private const string PARM_TYPECANCEL = "@TypeCancel";
        #endregion

        #region IOrders Members


        public int InsertNewOrder(OrdersOnlineModel order)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                            new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar, 10),
                            new SqlParameter(PARM_ORDERSIDE, SqlDbType.VarChar, 4),
                            new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 8),
                            new SqlParameter(PARM_ORDERSPEC, SqlDbType.VarChar, 30),
                            new SqlParameter(PARM_ORDERCLASS, SqlDbType.VarChar,30),
                            new SqlParameter(PARM_ORDERMODE, SqlDbType.VarChar, 30),
                            new SqlParameter(PARM_OPENDATE, SqlDbType.DateTime),
                            new SqlParameter(PARM_EXPIREDDATE, SqlDbType.DateTime),
                            new SqlParameter(PARM_ORDERNO, SqlDbType.VarChar, 20),
                            new SqlParameter(PARM_TIMESTAMP, SqlDbType.DateTime, 10),
                            new SqlParameter(PARM_PRICE, SqlDbType.VarChar, 6),
                            new SqlParameter(PARM_QUANTITY, SqlDbType.Int),
                            new SqlParameter(PARM_ORDERTYPE, SqlDbType.VarChar, 30)
                };
                parms[0].Value = order.AccountNo;
                parms[1].Value = order.OrderSide;
                parms[2].Value = order.Symbol;
                parms[3].Value = order.OrderSpec;
                parms[4].Value = order.OrderClass;
                parms[5].Value = order.OrderMode;
                parms[6].Value = order.OpenDate;
                parms[7].Value = order.ExpireDate;
                parms[8].Value = order.OrderNo;
                parms[9].Value = order.TimeStamp;
                parms[10].Value = order.Price;
                parms[11].Value = order.Quantity;
                parms[12].Value = order.OrderType;


                switch (order.OrderType)
                {
                    case "PLACE":
                        return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_ProcessNewOrder", parms));
                    case "CHANGE":
                        return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_InsertChangeOrder", parms));
                    case "CANCEL":
                        return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_InsertCancelOrder", parms));
                    default:
                        break;
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public OrdersOnlineModel getNewestOrder(string OrgStatus, string StatusAfter)
        {
            SqlParameter[] parms = new SqlParameter[]{
                                    new SqlParameter("@OrgStatus", SqlDbType.VarChar,20),
                                    new SqlParameter("@StatusAfter", SqlDbType.VarChar,20),
            };

            parms[0].Value = OrgStatus;
            parms[1].Value = StatusAfter;

            SqlDataReader rdr = null;
            try
            {
                rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetNewestOrder", parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<OrdersOnlineModel> getOrderListByStatus(string Status)
        {
            SqlDataReader rdr = null;
            List<OrdersOnlineModel> list = new List<OrdersOnlineModel>();
            try
            {
                SqlParameter parm1 = new SqlParameter("@Status", SqlDbType.VarChar, 20);
                parm1.Value = Status;
                rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetOrderListByStatus", parm1);
                while (rdr.Read())
                {
                    OrdersOnlineModel item = new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                    list.Add(item);
                }
                return list;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<OrdersOnlineModel> getOrderListByStatusAndAccountNo(string Status, string AccountNo)
        {
            SqlDataReader rdr = null;
            List<OrdersOnlineModel> list = new List<OrdersOnlineModel>();
            try
            {
                SqlParameter parm1 = new SqlParameter("@Status", SqlDbType.VarChar, 20);
                parm1.Value = Status;
                SqlParameter parm2 = new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar, 10);
                parm2.Value = AccountNo;

                SqlParameter[] parms = { parm1, parm2 };

                rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetOrderListByStatusAndAccountNo", parms);

                while (rdr.Read())
                {
                    OrdersOnlineModel item = new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                rdr.Close();
            }
        }


        public List<OrdersOnlineModel> getOrderListDetail(string AccountNo, string OrderSide, string Symbol, string OrderSpec, string OrderClass, string OrderMode, DateTime TimeStamp, string Price, string OrderType, string OrderNo, int Quantity)
        {
            SqlDataReader rdr = null;
            List<OrdersOnlineModel> list = new List<OrdersOnlineModel>();
            try
            {
                string query = GET_ORDER_DETAIL + BuildConditions(AccountNo, OrderSide, Symbol, OrderSpec, OrderClass, OrderMode, TimeStamp, Price, OrderType, OrderNo, Quantity);

                rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);
                while (rdr.Read())
                {
                    OrdersOnlineModel item = new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                    list.Add(item);
                }
                return list;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        private string BuildConditions(string AccountNo, string OrderSide, string Symbol, string OrderSpec, string OrderClass, string OrderMode, DateTime TimeStamp, string Price, string OrderType, string OrderNo, int Quantity)
        {
            string temp = "";
            if (!string.IsNullOrEmpty(AccountNo))
                temp += " AND (AccountNo='" + AccountNo + "') ";
            if (!string.IsNullOrEmpty(OrderSide))
                temp += " AND (OrderSide='" + OrderSide + "') ";
            if (!string.IsNullOrEmpty(Symbol))
                temp += " AND (Symbol='" + Symbol + "') ";
            if (!string.IsNullOrEmpty(OrderClass))
                temp += " AND (OrderClass='" + OrderClass + "') ";
            if (!string.IsNullOrEmpty(OrderSpec))
                temp += " AND (OrderSpec='" + OrderSpec + "') ";
            if (!string.IsNullOrEmpty(OrderMode))
                temp += " AND (OrderMode='" + OrderMode + "') ";
            if (!string.IsNullOrEmpty(Price))
                temp += " AND (Price='" + Price + "') ";
            if (!string.IsNullOrEmpty(OrderType))
                temp += " AND (OrderType='" + OrderType + "') ";
            if (!string.IsNullOrEmpty(OrderNo))
                temp += " AND (OrderNo='" + OrderNo + "') ";

            if (Quantity != 0)
                temp += " AND (Quantity='" + Quantity + "') ";

            if (TimeStamp != null)
                temp += " AND (Datediff(day,TimeStamp,'" + TimeStamp.ToString("MM/dd/yyyy") + "')=0) ";
            return temp;

        }

        public List<OrdersOnlineModel> getPendingOrdersByAccountNo(string AccountNo, string Status)
        {
            SqlDataReader rdr = null;
            List<OrdersOnlineModel> list = new List<OrdersOnlineModel>();
            try
            {
                SqlParameter[] parms = new SqlParameter[]{
                                            new SqlParameter("@AccountNo", SqlDbType.VarChar, 10),
                                            new SqlParameter("@CompletedStatus", SqlDbType.VarChar, 20)
                };

                parms[0].Value = AccountNo;
                parms[1].Value = Status;

                rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetPendingOrdersByAccountNo", parms);
                while (rdr.Read())
                {
                    OrdersOnlineModel item = new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                rdr.Close();
            }
        }

        public int UpdateOrderStatus(int OrderPlaceId, string Status)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter("@LineNum", SqlDbType.Int),
                                        new SqlParameter("@Status", SqlDbType.VarChar, 20)
                };
                parms[0].Value = OrderPlaceId;
                parms[1].Value = Status;

                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_UpdateOrderStatus", parms));
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public OrdersOnlineModel GetOriginalOrder(int OrderPlaceId)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter parm1 = new SqlParameter("@OrderPlaceId", SqlDbType.Int);
                parm1.Value = OrderPlaceId;

                rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetOriginalOrderByOrderId", parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                }
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public OrdersOnlineModel GetOrderByOrderId(int OrderPlaceId)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter parm1 = new SqlParameter("@OrderPlaceId", SqlDbType.Int);
                parm1.Value = OrderPlaceId;

                rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetOrderByOrderId", parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                }
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }


        public OrdersOnlineModel GetLastOrderByOrderId(int OrderId, string Status)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter("@OrderId", SqlDbType.Int),
                                        new SqlParameter("@Status", SqlDbType.VarChar, 20)
                };
                parms[0].Value = OrderId;
                parms[1].Value = Status;

                rdr = SQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetMostRecentOrderByOrderId", parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                }
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }


        public OrdersOnlineModel GetMostRecentOrder(string AccountNo, string OrderSide, DateTime OpenDate, DateTime ExpireDate, string Symbol, string Price, int Quantity, string TypeCancel)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar, 10),
                                        new SqlParameter(PARM_ORDERSIDE, SqlDbType.VarChar, 5),
                                        new SqlParameter(PARM_OPENDATE, SqlDbType.DateTime),
                                        new SqlParameter(PARM_EXPIREDDATE,SqlDbType.DateTime),
                                        new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 8),
                                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar, 6),
                                        new SqlParameter(PARM_QUANTITY, SqlDbType.Int),
                                        new SqlParameter(PARM_TYPECANCEL, SqlDbType.VarChar,20)
                };
                parms[0].Value = AccountNo;
                parms[1].Value = OrderSide;
                parms[2].Value = OpenDate.Date;
                parms[3].Value = ExpireDate.Date;
                parms[4].Value = Symbol;
                parms[5].Value = Price;
                parms[6].Value = Quantity;
                parms[7].Value = TypeCancel;

                rdr = SQLHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_GetMostRecentOrder", parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new OrdersOnlineModel(Convert.ToInt32(rdr["LINENUM"]),
                                    Convert.ToInt32(rdr["ORDERID"]),
                                    rdr["ACCOUNTNO"].ToString(), rdr["ORDERSIDE"].ToString(),
                                    rdr["SYMBOL"].ToString(), rdr["ORDERSPEC"].ToString(),
                                    rdr["ORDERCLASS"].ToString(), rdr["ORDERMODE"].ToString(),
                                    Convert.ToDateTime(rdr["OPENDATE"]),
                                    Convert.ToDateTime(rdr["EXPIREDDATE"]), rdr["ORDERNO"].ToString(),
                                    Convert.ToDateTime(rdr["TIMESTAMP"]), rdr["PRICE"].ToString(),
                                    Convert.ToInt32(rdr["QUANTITY"]), rdr["ORDERTYPE"].ToString());
                }
                else
                    return null;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }

        }

        public int UpdateChangeOrder(int OrderPlacedId, string Price)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter("@OrderPlacedId", SqlDbType.Int),
                                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar, 8)
                };
                parms[0].Value = OrderPlacedId;
                parms[1].Value = Price;

                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_UpdateChangeOrder", parms));
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public int InsertChangeOrder(int OrderId, string OrderNo, DateTime TimeStamp, int Quantity, string Price, string OrderType)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter(PARM_ORDERID, SqlDbType.Int),
                                        new SqlParameter(PARM_ORDERNO, SqlDbType.VarChar, 20),
                                        new SqlParameter(PARM_TIMESTAMP, SqlDbType.DateTime),
                                        new SqlParameter(PARM_QUANTITY, SqlDbType.Int),
                                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar,8),
                                        new SqlParameter(PARM_ORDERTYPE, SqlDbType.VarChar,6)
                };
                parms[0].Value = OrderId;
                parms[1].Value = OrderNo;
                parms[2].Value = TimeStamp;
                parms[3].Value = Quantity;
                parms[4].Value = Price;
                parms[5].Value = OrderType;


                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_InsertChangeOrder", parms));
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public int InsertCancelOrder(int OrderId, string OrderNo, DateTime TimeStamp, int Quantity, string Price, string OrderType)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                        new SqlParameter(PARM_ORDERSIDE, SqlDbType.Int),
                                        new SqlParameter(PARM_ORDERNO, SqlDbType.VarChar, 20),
                                        new SqlParameter(PARM_TIMESTAMP, SqlDbType.DateTime),
                                        new SqlParameter(PARM_QUANTITY, SqlDbType.Int),
                                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar,8),
                                        new SqlParameter(PARM_ORDERTYPE, SqlDbType.VarChar,6)
                };
                parms[0].Value = OrderId;
                parms[1].Value = OrderNo;
                parms[2].Value = TimeStamp;
                parms[3].Value = Quantity;
                parms[4].Value = Price;
                parms[5].Value = OrderType;


                return Convert.ToInt32(SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_InsertCancelOrder", parms));
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public void DeleteOrderByOrdePlacerId(int OrderPlaceId)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter("@OrderPlaceId", SqlDbType.Int);
                parm1.Value = OrderPlaceId;

                SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "_DeleteOrderByOrdePlacerId", parm1);
 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
